package com.book.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.book.domain.Book;
import com.book.domain.SplitPage;
import com.book.util.DBUtil;

public class BookDao {
	public void addBook(Book book) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		String sql = "insert into t_book(bookname,authorname,publishername,bookcatalog,bookimgurl,bookoutline) values(?,?,?,?,?,?)";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, book.getBookname());
			preparedStatement.setString(2, book.getAuthorname());
			preparedStatement.setString(3, book.getPublishername());
			preparedStatement.setString(4, book.getBookcatalog());
			preparedStatement.setString(5, book.getBookimgurl());
			preparedStatement.setString(6, book.getBookoutline());
			preparedStatement.executeUpdate();
			DBUtil.closeConnection(preparedStatement, null, connection);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Book> listAllBooks() {
		List<Book> bookslist = new ArrayList<Book>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "select * from t_book";
		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Book book = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"));
				bookslist.add(book);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			DBUtil.closeConnection(preparedStatement, resultSet, connection);
		}
		return bookslist;

	}

	public Book selectBook(Book book) {
		Connection connection = DBUtil.getConnection();
		String sql = "select * from t_book where id=?";
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Book book1 = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, book.getId());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				book1 = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"),
						resultSet.getFloat("bookprice"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return book1;
	}

	public List<Book> queryBooks(Book book) {
		Connection connection = DBUtil.getConnection();
		String sql = "select * from t_book where id=? or bookname=? or authorname=? or publishername=? or bookcatalog=? or bookoutline=? or bookimgurl=? ";
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Book> bookslist = new ArrayList<Book>();
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, book.getId());
			preparedStatement.setString(2, book.getBookname());
			preparedStatement.setString(3, book.getAuthorname());
			preparedStatement.setString(4, book.getPublishername());
			preparedStatement.setString(5, book.getBookcatalog());
			preparedStatement.setString(6, book.getBookoutline());
			preparedStatement.setString(7, book.getBookimgurl());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Book book1 = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"));
				bookslist.add(book1);
				System.out.println(book1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bookslist;
	}

	public List<Book> splitpageBooks(SplitPage splitPage) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Book> booksList = new ArrayList<Book>();
		String sql = "select * from( select *, ROW_NUMBER() over(order by id) as rownumber  from t_book ) as t where rownumber between ? and ?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, splitPage.getBeginrecord());
			preparedStatement.setInt(2, splitPage.getEndrecord());
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Book book1 = new Book(resultSet.getInt("id"),
						resultSet.getString("bookname"),
						resultSet.getString("authorname"),
						resultSet.getString("publishername"),
						resultSet.getString("bookcatalog"),
						resultSet.getString("bookimgurl"),
						resultSet.getString("bookoutline"));
				booksList.add(book1);
				System.out.println(book1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return booksList;
	}

	public int totalRecord(Book book) {
		Connection connection = DBUtil.getConnection();
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql="select count(*) from t_book  ";
		int totalrecord=0;
		try {
			
			preparedStatement = connection.prepareStatement(sql);
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				totalrecord=resultSet.getInt(1);
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("总记录数totalrecord="+totalrecord);
		return totalrecord;
	}
}
